# create table book(
#     id int primary key auto_increment,
#     book_name char(20) not null,
#     author char(12) not null,
#     press char(20) not null,
#     price float(6,2),
#     pub_date date
# );

# 写入数据
# import pymysql
# conn = pymysql.Connection(host='127.0.0.1', user='root', password="123",
#                  database='day41')
# cur = conn.cursor()
# with open('data',encoding='utf-8') as f:
#     try:
#         for line in f:
#             line = line.strip()
#             if '\t' in line:
#                 lst = line.split('\t')
#             elif '|' in line:
#                 lst = line.split('|')
#             sql = 'insert into book(book_name,author,press,price,pub_date) values (%s,%s,%s,%s,%s);'
#             cur.execute(sql,lst)
#     except:
#         conn.rollback()
# conn.commit()
# cur.close()
# conn.close()

# select book_name,price from book where author = 'egon'

# 2.找出最贵的图书的价格
# select max(price) from book;
# select price,book_name from book order by price desc limit 1;

# 3.求所有图书的均价
# select avg(price) from book;

# 4.将所有图书按照出版日期排序
# select * from book order by pub_date;

# 5.查询alex写的所有书的平均价格
# select avg(price) from book where author = 'alex'

# 扩展: 求所有人自己出版的图书的平均价格
# select author,avg(price) from book group by author
# 扩展: 求所有人自己出版的图书的平均价格>30的所有人
# select author from book group by author having  avg(price)>30

# 6.查询人民音乐不好听出版社出版的所有图书
# select * from book where press = '人民音乐不好听出版社';

# 7.查询人民音乐出版社出版的alex写的所有图书和价格
# select * from book where press = '人民音乐不好听出版社' and author = 'alex';

# 8.找出出版图书均价最高的作者
# select author,avg(price) as avg_p from book group by author order by avg_p desc limit 1;

# 9.找出最新出版的图书的作者和出版社
# select author,press from book order by pub_date desc limit 1;

# 10.显示各出版社出版的所有图书
# select press,group_concat(book_name) from book group by press;

# 11.查找价格最高的图书，并将它的价格修改为50元
# select max(price) from book;
# update book set price = 50 where price = 70

# 12.删除价格最低的那本书对应的数据
# select min(price) from book;
# delete from book where price = 5;

# 13.将所有alex写的书作者修改成alexsb
# update book set author = 'alexsb' where author = 'alex';

# 14.select year(publish_date) from book
# 自己研究上面sql语句中的year函数的功能，完成需求：
# 将所有2017年出版的图书从数据库中删除
# delete from book where year(publish_date) = 2017;








